package com.jrsoft.bim.component.poi;

import com.alibaba.fastjson.JSON;
import com.jrsoft.bim.annotation.ExcelColumn;
import com.jrsoft.bim.dto.excel.ComponentDTO;
import com.jrsoft.bim.dto.excel.FieldDTO;
import com.jrsoft.bim.enums.ComponentCategoryEnum;
import com.jrsoft.bim.util.FileUtil;
import com.jrsoft.bim.util.StringUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.Stream;

/**
 * excel处理
 *
 * @author Blueeyedboy
 * @create 2019-12-20 11:01 AM
 **/
@Slf4j
public class ExcelUtil {

	private final static String EXCEL2003 = "xls";
	private final static String EXCEL2007 = "xlsx";
	private final static int COMPONENT_NAME_ROW = 1;
	private final static int COMPONENT_NAME_COL = 6;
	private final static int COMPONENT_CODE_ROW = 3;
	private final static int COMPONENT_CODE_COL = 6;
	private final static int COMPONENT_TYPE_ROW = 2;
	private final static int COMPONENT_TYPE_COL = 6;
	/**
	* (二）坐标及表面几何参数
	* @create 12/20/2019 11:36 AM
	**/
	private final static String CONTENT2_FLAG = "坐标及表面几何参数";

	/**
	 * (三）钢筋关系参数
	 * @create 12/20/2019 11:36 AM
	 **/
	private final static String CONTENT3_FLAG = "钢筋关系参数";

	public static Map<ComponentCategoryEnum,List<ComponentDTO>> parse(String filePath){
		File file = new File(filePath);
		String path = Optional.ofNullable(file).filter(o->o.exists()).orElseThrow(()->new RuntimeException("文件路径不存在:"+filePath)).getAbsolutePath();
		Map<ComponentCategoryEnum,List<ComponentDTO>> result = new HashMap<>();
		try {
			Files.walk(Paths.get(path)).filter(o->!Files.isDirectory(o))
					.filter(f->FileUtil.isExcel(f.toFile().getName()))
					.filter(f-> !f.getFileName().toString().startsWith("~$"))
					.forEach(f -> {
						String fileName = f.getFileName().toString();


				ComponentCategoryEnum categoryEnum = ComponentCategoryEnum.get(fileName);
//				if(categoryEnum.equals(ComponentCategoryEnum.BRIDGE)){
					log.info(categoryEnum.getValue()+"-----开始解析-----"+fileName);

//				}
				List<ComponentDTO> data = read(f.toAbsolutePath().toString());
				if(data != null && data.size()>0){
					List<ComponentDTO> dtoList = result.get(categoryEnum);
					if(dtoList == null){
						dtoList = new ArrayList<>();
						result.put(categoryEnum,dtoList);
					}
					if(categoryEnum.equals(ComponentCategoryEnum.BRIDGE)){
//						System.out.println("-----dtoList--1---"+dtoList.size());

					}
					dtoList.addAll(data);
					if(categoryEnum.equals(ComponentCategoryEnum.BRIDGE)){
//						System.out.println("-----dtoList--2---"+dtoList.size());

					}
				}
			});
		} catch (IOException e) {
			e.printStackTrace();
		}
		return result;
	}


	public static List<ComponentDTO> read(String filePath){
		File f = new File(filePath);
		String fileName = f.getName();
		if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
			log.error("上传文件格式不正确");
		}
		try {
			return read(new FileInputStream(f), FileUtil.getSuffix(fileName));
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}
		return null;
	}

	public static List<ComponentDTO> read(InputStream in, String suffix){
		// 构件
		List<ComponentDTO> data = new ArrayList<>();

		Workbook workbook = null;
		try {
			if (suffix.endsWith(EXCEL2007)) {
				//                FileInputStream is = new FileInputStream(new File(path));
				workbook = new XSSFWorkbook(in);
			}
			if (suffix.endsWith(EXCEL2003)) {
				//                FileInputStream is = new FileInputStream(new File(path));
				workbook = new HSSFWorkbook(in);
			}
			Map<String,Field> fieldMap = new HashMap<>();
			Map<Integer,String> fieldCellNumMap  = new HashMap<>();
			List<Field> fields = Stream.of(FieldDTO.class.getDeclaredFields()).collect(Collectors.toList());
			fields.forEach(
					field -> {
						ExcelColumn annotation = field.getAnnotation(ExcelColumn.class);
						if (annotation != null) {
							String value = annotation.value();
							if (StringUtils.isBlank(value)) {
								return;//return起到的作用和continue是相同的 语法
							}
							field.setAccessible(true);
							fieldMap.put(value,field);
						}
					}
			);

			if (workbook != null) {
				//类映射  注解 value-->bean columns
				//默认读取第一个sheet
				int sheetNumber = workbook.getNumberOfSheets();
//				System.out.println("-----sheetNumber-------"+sheetNumber);
				if(sheetNumber>0){
					for(int m = 0;m<sheetNumber;m++){
						Sheet sheet = workbook.getSheetAt(m);

						int firstRowNum = sheet.getFirstRowNum();
						int lastRowNum = sheet.getLastRowNum();
						if(lastRowNum==0) {

							log.error("导入文件数据为空");
						}

						ComponentDTO componentDTO = new ComponentDTO();

						List<FieldDTO> dataList = new ArrayList<>();

						boolean isContent2Start = false;
						boolean isContentEnd = false;
						// 是否标题行
						boolean isContentTitleRow = false;
						// 标题行号
						int contentTitleRowNum = 0;

						for (int i = firstRowNum; i <= lastRowNum; i++) {

							Row row = sheet.getRow(i);
							//忽略空白行
							if (row == null) {
								continue;
							}
							int firstCellNum = row.getFirstCellNum();
							int lastCellNum = row.getLastCellNum();
							if(firstCellNum == -1){
								continue;
							}



							FieldDTO c = null;
							// 判断是否标题行
							if(contentTitleRowNum == i){
								isContentTitleRow = true;
							}
							//					System.out.println(firstCellNum+"---------"+lastCellNum);
							for (int j = firstCellNum; j <= lastCellNum; j++) {

								Cell cell = row.getCell(j);
								String value = getCellValue(cell);
//								log.info("{}行{}列值：{}",i,j,value);
								// 构件名称
								if(i == COMPONENT_NAME_ROW && j == COMPONENT_NAME_COL){
									componentDTO.setName(value);
								}
								// 构件编号
								if(i == COMPONENT_CODE_ROW && j == COMPONENT_CODE_COL){
									componentDTO.setCode(value);
								}
								// 构件分类编码
								if(i == COMPONENT_TYPE_ROW && j == COMPONENT_TYPE_COL){
									componentDTO.setType(value);
								}
								if(StringUtil.isNotBlank(value) && value.trim().indexOf(CONTENT2_FLAG)>-1){
									isContent2Start = true;
									contentTitleRowNum = i+1;
									break;
								}
								if(StringUtil.isNotBlank(value) && value.trim().indexOf(CONTENT3_FLAG)>-1){
									isContentEnd = true;
									isContent2Start = false;
									isContentTitleRow = false;
									break;
								}
								if(isContent2Start && !isContentEnd){
									//							log.info("{}行{}列值：{}",i,j,value);
									if(isContentTitleRow){
										if(StringUtil.isNotBlank(value)){
											//									System.out.println(j+"---------"+value);
											fieldCellNumMap.put(j,value.trim());
										}
									}else{
										//								System.out.println(j+"----------"+fieldCellNumMap.containsKey(j));
										if(fieldCellNumMap.containsKey(j)){
											String title = fieldCellNumMap.get(j);
											Field field = fieldMap.get(title);
											if(field != null){
												if(c == null){
													c = new FieldDTO();
												}

												//										System.out.println(title+"-----"+value+"------");
												setFieldValue(c,field,value);
											}
										}
									}
								}
							}

							if(isContentTitleRow){
								isContentTitleRow = false;
								//						System.out.println("----fieldCellNumMap-----"+fieldCellNumMap);
							}
							if(isContent2Start){
								//						log.info("----------------{}",c);
						/*if(c != null){
							dataList.add(c);
						}*/
								Optional.ofNullable(c).map((o)->{
									componentDTO.getFieldDTOMap().put(o.getCode().trim().toUpperCase(),o);
									return dataList.add(o);
								});
							}

							if(isContentEnd){
								break;
							}
						}
						componentDTO.setFieldDTOList(dataList);
						data.add(componentDTO);
//						dataList.forEach(System.out::println);
					}
				}

			}
//			System.out.println("-----dataList------"+dataList.size());
		}catch (Exception e) {
			log.error(String.format("parse excel exception!"), e);
		} finally {
			if (workbook != null) {
				try {
					workbook.close();
				} catch (Exception e) {
					log.error(String.format("parse excel exception!"), e);
				}
			}
			return data;
		}
	}

	/**
	 * 通过反射设置对象中字段的值
	 * 如果设置成功，则返回设置的字段值
	 * @param
	 * @return
	 * @author Blueeyedboy
	 * @create 1/16/2019 4:33 PM
	 **/
	public static Object setFieldValue(Object object, Field f, Object val) throws RuntimeException {
		Object result = null;
		try {
			// 设置对象属性域的属性值
			f.set(object, val);
			// 获取对象属性域的属性值
			result = f.get(object);
		}catch(Exception e){
			e.printStackTrace();
			throw new RuntimeException("反射设置字段值报错:"+e.getMessage());
		}
		return result;
	}

	private static String getCellValue(Cell cell) {

		String result = null;
		if (cell == null) {
			result =  "";
			return result;
		}
		if (cell.getCellType() == CellType.NUMERIC) {
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				result =  HSSFDateUtil.getJavaDate(cell.getNumericCellValue()).toString();
			} else {
//				System.out.println("--------------"+cell.getNumericCellValue());
//				return new BigDecimal(cell.getNumericCellValue()).toString();
				result =  String.valueOf(cell.getNumericCellValue());
			}
		} else if (cell.getCellType() == CellType.STRING) {
			result =  StringUtils.trimToEmpty(cell.getStringCellValue());
		} else if (cell.getCellType() == CellType.FORMULA) {
//			result =  StringUtils.trimToEmpty(cell.getCellFormula());
			try {
				result = cell.getStringCellValue();
			} catch (IllegalStateException e) {
				result = String.valueOf(cell.getNumericCellValue());
			}
			if(result.indexOf(".")>-1 && result.substring(result.indexOf(".")+1).length()>2){
				result =  new BigDecimal(result).setScale(2, BigDecimal.ROUND_HALF_UP).toString();
			}

		} else if (cell.getCellType() == CellType.BLANK) {
			result =  "";
		} else if (cell.getCellType() == CellType.BOOLEAN) {
			result =  String.valueOf(cell.getBooleanCellValue());
		} else if (cell.getCellType() == CellType.ERROR) {
			result =  "";
		} else {
			result =  cell.toString().trim();
		}
//		System.out.println(cell.getCellType()+"-----------"+result);
		return result;
	}

	public static void main(String[] args) {
		String filePath = "E:\\工作\\项目\\bim\\开发\\cdm测试\\板-双柱洞口.xlsx";
//		ExcelUtil.read(filePath);

		filePath = "E:\\工作\\项目\\bim\\开发\\cdm测试";
		Map<ComponentCategoryEnum,List<ComponentDTO>> result = ExcelUtil.parse(filePath);

	}


}
